﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SoufunData.DB;

namespace SoufunData
{
    public partial class ExToDbLandSupportForm : DevExpress.XtraEditors.XtraForm
    {
        public ExToDbLandSupportForm()
        {
            InitializeComponent();
        }

        private void btn_excelPath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {

            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            string fileName = openFileDialog1.FileName;
            btn_excelPath.Text = fileName;

            gridControl2.DataSource = null;
            gridView2.Columns.Clear();
            gridView2.GroupPanelText = "请选择Excel文件";
            if (fileName == "")
            {
                return;
            }
            DataSet ds = Common.LoadDataFromExcel(fileName);
            if (ds == null || ds.Tables.Count == 0)
            {
                string strError = "读取Excel时发生错误，请核对：\r\n ";
                strError += "1，导入时请在Excel中关闭想要导入的文件！\r\n";
                strError += "2，导入的Excel表中的工作表名称必须为Sheet1,否者程序无法读取！\r\n";
                strError += "3，请确保您的计算机安装了Microsoft Office！\r\n";
                strError += "4，请确保导入的Excel文件正常！";
                XtraMessageBox.Show(strError);
                btn_excelPath.Text = "";
                return;
            }            
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {
                btn_excelPath.Text = "";
                return;
            }
            DataTable dt = ds.Tables[0].Copy();
            dt.Clear();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                if (row[0].ToString() == "" && row[1].ToString() == ""
                    && row[2].ToString() == "")
                {
                    continue;
                }
                dt.Rows.Add(row.ItemArray);
            }
            gridControl2.DataSource = dt;
            gridView2.GroupPanelText = "共有" + dt.Rows.Count + "条数据";
        }


        private void AddData()
        {
           
            DataTable dtOld = (DataTable)gridControl2.DataSource;

            DataTable dt = dtOld.Copy();
            dt.Rows.Clear();
            foreach (DataRow dataRow in dtOld.Rows)
            { 
                dt.Rows.Add(dataRow.ItemArray);
            }

            SqlClass sqlClass = new SqlClass();
            List<string> existProject = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                string parcelId = row["地块编号"].ToString();
                if (sqlClass.LandSupplyExistId(parcelId))
                {
                    existProject.Add(parcelId);
                }
            }
            if (existProject.Count > 0)
            {
                StringBuilder strWarming = new StringBuilder();
                strWarming.Append("您选择的Excel表格中的以下地块在数据库中已存在,不能再次添加：\r\n");
                foreach (string sproName in existProject)
                {
                    strWarming.Append(sproName);
                    strWarming.Append("\r\n");
                }
                XtraMessageBox.Show(strWarming.ToString());
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    btn_excelPath.Enabled = true;
                    btn_add.Enabled = true;
                }));
                return;
            }


            int insertRowsCount = 500;//一次性插入数据库行数
            int succeedrows = 0;//成功插入数据行数
            StringBuilder strSql = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i==12)
                {
                    Console.WriteLine("222");
                }
                DataRow row = dt.Rows[i];
                string strCTime = row["公告时间"].ToString();
                string strLandParcelID = row["地块编号"].ToString();
                string strLandlocation = row["土地位置"].ToString();
                string strAdministratiVeregion = row["行政区"].ToString();
                string strBoard = row["版块"].ToString();
                string strPointsLoop = row["分环线"].ToString();
                string strTotalAreaOfUsedLand = row["总用地面积（㎡）"].ToString();
                if (strTotalAreaOfUsedLand.Trim() == "" || 
                    strTotalAreaOfUsedLand=="-")
                {
                    strTotalAreaOfUsedLand = "-1";
                }
                string strConstructionLandArea = row["建设用地面积（㎡）"].ToString();
                if (strConstructionLandArea.Trim()==""||
                    strConstructionLandArea=="-")
                {
                    strConstructionLandArea = "-1";
                }
                string strPlanningConstructionArea = row["规划建筑面积（㎡）"].ToString();
                if (strPlanningConstructionArea.Trim()==""||
                    strPlanningConstructionArea=="-")
                {                    
                    strPlanningConstructionArea = "-1";
                }
                string strGenerationOfLandArea = row["代征地面积（㎡）"].ToString();
                if (strGenerationOfLandArea.Trim()==""||
                    strGenerationOfLandArea=="-")
                {
                    strGenerationOfLandArea = "-1";
                }
                string strPlotRatio = row["容积率"].ToString();
                string strDensityOfBuilding = row["建筑密度"].ToString();
                string strTheCommercialScale = row["商业比例"].ToString();
                string strLandUse = row["土地用途"].ToString();
                string strTransferYear = row["出让年限"].ToString();
                string strBiddingDeposit = row["竞买保证金"].ToString();
                if (strBiddingDeposit.Trim()==""||
                    strBiddingDeposit=="-")
                {
                    strBiddingDeposit = "-1";
                }
                string strStartingPrice = row["起始价（万元）"].ToString();
                if (strStartingPrice.Trim()==""||
                    strStartingPrice=="-")
                {
                    strStartingPrice = "-1";
                }
                string strBidIncrement = row["加价幅度"].ToString();
                string strTheOriginalLandUser = row["原土地使用人"].ToString();
                string strLandStatus = row["土地性质"].ToString();
                string strIfHasSecurityHousing = row["是否配建保障房"].ToString();
                string strSecurityHousingSituation = row["配建保障房情况"].ToString();
                string strSecurityHousingArea = row["保障房面积"].ToString();
                string strSecurityHousingType = row["保障房类型（直接填写对应类型名称，经济适用房、廉租房、公共租赁房、定向安置房、限价房）"].ToString();
                string strStartTime = row["起始时间"].ToString();
                string strEndTime = row["截止时间"].ToString();
                string strAnnouncementID = row["公告编号"].ToString();
                string strTransferDocuments = row["出让文件"].ToString();
                string strGongdiMap = row["供地附图"].ToString();
                string strGongdiType = row["供地方式"].ToString();
                string strState = row["状态"].ToString();
                string strFourrange = row["四至范围"].ToString();
                string strPlanedUse = row["规划用途"].ToString();
                 
                
                if (i % insertRowsCount == 0)
                {
                    strSql = new StringBuilder();
                    strSql.Append("Insert into tlandsupply (");
                    strSql.Append("CTime,");
                    strSql.Append("LandParcelID,");
                    strSql.Append("Landlocation,");
                    strSql.Append("AdministratiVeregion,");
                    strSql.Append("Board,");
                    strSql.Append("PointsLoop,");
                    strSql.Append("TotalAreaOfUsedLand,");
                    strSql.Append("ConstructionLandArea,");
                    strSql.Append("PlanningConstructionArea,");
                    strSql.Append("GenerationOfLandArea,");
                    strSql.Append("PlotRatio,");
                    strSql.Append("DensityOfBuilding,");
                    strSql.Append("TheCommercialScale,");
                    strSql.Append("LandUse,");
                    strSql.Append("TransferYear,");
                    strSql.Append("BiddingDeposit,");
                    strSql.Append("StartingPrice,");
                    strSql.Append("BidIncrement,");
                    strSql.Append("TheOriginalLandUser,");
                    strSql.Append("LandStatus,");
                    strSql.Append("IfHasSecurityHousing,");
                    strSql.Append("SecurityHousingSituation,");
                    strSql.Append("SecurityHousingArea,");
                    strSql.Append("SecurityHousingType,");
                    strSql.Append("StartTime,");
                    strSql.Append("EndTime,");
                    strSql.Append("AnnouncementID,");
                    strSql.Append("TransferDocuments,");
                    strSql.Append("GongdiMap,");
                    strSql.Append("GongdiType,");
                    strSql.Append("State,");
                    strSql.Append("Fourrange,PlanedUse)");
                     
                    strSql.Append(" value  ");
                }
                strSql.Append("(");
                strSql.Append("'");
                strSql.Append(strCTime);
                strSql.Append("','");
                strSql.Append(strLandParcelID);
                strSql.Append("','");
                strSql.Append(strLandlocation);
                strSql.Append("','");
                strSql.Append(strAdministratiVeregion);
                strSql.Append("','");
                strSql.Append(strBoard);
                strSql.Append("','");
                strSql.Append(strPointsLoop);
                strSql.Append("','");
                strSql.Append(strTotalAreaOfUsedLand);
                strSql.Append("',");
                strSql.Append(strConstructionLandArea);
                strSql.Append(",");
                strSql.Append(strPlanningConstructionArea);
                strSql.Append(",");
                strSql.Append(strGenerationOfLandArea);
                strSql.Append(",'");
                strSql.Append(strPlotRatio);
                strSql.Append("','");
                strSql.Append(strDensityOfBuilding);
                strSql.Append("','");
                strSql.Append(strTheCommercialScale);
                strSql.Append("','");
                strSql.Append(strLandUse);              
                strSql.Append("','");
                strSql.Append(strTransferYear);
                strSql.Append("',");
                strSql.Append(strBiddingDeposit);
                strSql.Append(",");
                strSql.Append(strStartingPrice);
                strSql.Append(",'");
                strSql.Append(strBidIncrement);
                strSql.Append("','");
                strSql.Append(strTheOriginalLandUser);
                strSql.Append("','");
                strSql.Append(strLandStatus);
                strSql.Append("','");
                strSql.Append(strIfHasSecurityHousing);
                strSql.Append("','");
                strSql.Append(strSecurityHousingSituation);
                strSql.Append("','");
                strSql.Append(strSecurityHousingArea);
                strSql.Append("','");
                strSql.Append(strSecurityHousingType);
                strSql.Append("','");
                strSql.Append(strStartTime);
                strSql.Append("','");
                strSql.Append(strEndTime);
                strSql.Append("','");
                strSql.Append(strAnnouncementID);
                strSql.Append("','");
                strSql.Append(strTransferDocuments);
                strSql.Append("','");
                strSql.Append(strGongdiMap);
                strSql.Append("','");
                strSql.Append(strGongdiType);
                strSql.Append("','");
                strSql.Append(strState);
                strSql.Append("','");
                strSql.Append(strFourrange);
                strSql.Append("','");
                strSql.Append(strPlanedUse);              
                strSql.Append("'),");

                if ((i + 1) % insertRowsCount == 0
                    || i == dt.Rows.Count - 1)
                {
                    strSql.Remove(strSql.Length - 1, 1);
                    try
                    {
                        succeedrows += DbHelper.ExecuteSql(strSql.ToString());
                    }
                    catch (Exception ex)
                    {
                        XtraMessageBox.Show(ex.Message);
                    }

                }
            }

            if (dt.Rows.Count == succeedrows)
            {
                XtraMessageBox.Show("添加成功！");
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    gridView2.GroupPanelText = "请选择想要导入的Excel表！";
                }));
            }
            else
            {
                int failedCount = dt.Rows.Count - succeedrows;
                XtraMessageBox.Show("共记" + dt.Rows.Count + "条数据，其中"
                    + failedCount + "条数据添加失败！");
            }
            BeginInvoke(new MethodInvoker(() =>
            {
               
                btn_excelPath.Enabled = true;
                btn_add.Enabled = true;
            }));
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            if (btn_excelPath.Text.Trim()=="")
            {
                XtraMessageBox.Show("请先选择Excel！");
                return;
            }
            if (!CheckDataValid())
            {
                return;
            }
             
            btn_excelPath.Enabled = false;
            btn_add.Enabled = false;
            Thread t = new Thread(AddData) { IsBackground = true };
            t.Start();
        }

        #region 数据完整性检测
        private bool CheckDataValid()
        {
            DataTable dt = (DataTable)gridControl2.DataSource;
            StringBuilder strErrors = new StringBuilder();
           
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strLandParcelID = dr["地块编号"].ToString();
              //  string strXzq = dr["行政区"].ToString();
               // string strBoard = dr["版块"].ToString();
               // string strPointsLoop = dr["分环线"].ToString();
                string strPlandUse = dr["规划用途"].ToString();
                string strLandStatus = dr["土地性质"].ToString();
                string strState = dr["状态"].ToString();
                string strCTime = dr["公告时间"].ToString();
                string strStartTime = dr["起始时间"].ToString();
                string strEndTime = dr["截止时间"].ToString();
                  
                if (strLandParcelID.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【地块编号】为空！\r\n"); 
                }
                DateTime dateTemp;
                if (!DateTime.TryParse(strCTime, out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【公告时间】不是正确的时间格式！\r\n");
                }
                if (!DateTime.TryParse(strStartTime, out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【起始时间】不是正确的时间格式！\r\n");
                }
                if (!DateTime.TryParse(strEndTime, out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【截止时间】不是正确的时间格式！\r\n");
                }
                //if (strXzq.Trim() == "")
                //{
                //    strErrors.Append("第" + (i + 2) + "行数据中的【行政区】为空！\r\n");
                //}
                //if (strBoard.Trim() == "")
                //{
                //    strErrors.Append("第" + (i + 2) + "行数据中的【版块】为空！\r\n");
                //}
                //if (strPointsLoop.Trim() == "")
                //{
                //    strErrors.Append("第" + (i + 2) + "行数据中的【分环线】为空！\r\n");
                //}
                if (strPlandUse.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【规划用途】为空！\r\n");
                }
                if (strLandStatus.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【土地性质】为空！\r\n");
                }
                if (strState.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【状态】为空！\r\n");
                }
                string strTotalAreaOfUsedLand = dr["总用地面积（㎡）"].ToString();
                string strConstructionLandArea = dr["建设用地面积（㎡）"].ToString();
                string strPlanningConstructionArea = dr["规划建筑面积（㎡）"].ToString();
                string strGenerationOfLandArea = dr["代征地面积（㎡）"].ToString();
                
                string strBiddingDeposit = dr["竞买保证金"].ToString();
                string strStartingPrice = dr["起始价（万元）"].ToString();
               

                float floatTemp;
                if (strTotalAreaOfUsedLand.Trim()!="" &&
                    strTotalAreaOfUsedLand!="-"&&
                    !float.TryParse(strTotalAreaOfUsedLand, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【总用地面积】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if ( strConstructionLandArea.Trim()!="" &&
                     strConstructionLandArea != "-" &&
                    !float.TryParse(strConstructionLandArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【建设用地面积（㎡）】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strPlanningConstructionArea.Trim() != "" &&
                  strPlanningConstructionArea != "-" &&
                 !float.TryParse(strPlanningConstructionArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【规划建筑面积】的数据格式有误，不是正确的数字格式！\r\n");
                }

                if (strGenerationOfLandArea.Trim() != "" &&
                  strGenerationOfLandArea != "-" &&
                 !float.TryParse(strGenerationOfLandArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【代征地面积（㎡）】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strBiddingDeposit.Trim() != "" &&
                 strBiddingDeposit != "-" &&
                !float.TryParse(strBiddingDeposit, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【竞买保证金】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strStartingPrice.Trim() != "" &&
                  strStartingPrice != "-" &&
                 !float.TryParse(strStartingPrice, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【起始价（万元）】的数据格式有误，不是正确的数字格式！\r\n");
                }
               
            

               
            }
            if (strErrors.ToString() != "")
            {                
                XtraMessageBox.Show("您选择的Excel中的数据存在以下错误：\r\n"+
                    strErrors);
                return false;
            }              
            return true;
        }
         
        private bool CheckExcelColumnsName(DataTable dt)
        {
            StringBuilder strErrors = new StringBuilder();
            if (!dt.Columns.Contains("公告时间"))
            {
                strErrors.Append("【公告时间】、");
            }
            if (!dt.Columns.Contains("地块编号"))
            {
                strErrors.Append("【地块编号】、");
            }
            if (!dt.Columns.Contains("土地位置"))
            {
                strErrors.Append("【土地位置】、");
            }
            if (!dt.Columns.Contains("行政区"))
            {
                strErrors.Append("【行政区】、");
            }
            if (!dt.Columns.Contains("版块"))
            {
                strErrors.Append("【版块】、");
            }
            if (!dt.Columns.Contains("分环线"))
            {
                strErrors.Append("【分环线】、");
            }
            if (!dt.Columns.Contains("总用地面积（㎡）"))
            {
                strErrors.Append("【总用地面积（㎡）】、");
            }
            if (!dt.Columns.Contains("建设用地面积（㎡）"))
            {
                strErrors.Append("【建设用地面积（㎡）】、");
            }
            if (!dt.Columns.Contains("规划建筑面积（㎡）"))
            {
                strErrors.Append("【规划建筑面积（㎡）】、");
            }
            if (!dt.Columns.Contains("代征地面积（㎡）"))
            {
                strErrors.Append("【代征地面积（㎡）】、");
            }
            if (!dt.Columns.Contains("容积率"))
            {
                strErrors.Append("【容积率】");
            }
            if (!dt.Columns.Contains("建筑密度"))
            {
                strErrors.Append("【建筑密度】");
            }
            if (!dt.Columns.Contains("商业比例"))
            {
                strErrors.Append("【商业比例】");
            }
            if (!dt.Columns.Contains("规划用途"))
            {
                strErrors.Append("【规划用途】");
            }
            if (!dt.Columns.Contains("土地用途"))
            {
                strErrors.Append("【土地用途】");
            }
            if (!dt.Columns.Contains("出让年限"))
            {
                strErrors.Append("【出让年限】");
            }
            if (!dt.Columns.Contains("竞买保证金"))
            {
                strErrors.Append("【竞买保证金】");
            }
            if (!dt.Columns.Contains("起始价（万元）"))
            {
                strErrors.Append("【起始价（万元）】");
            }
            if (!dt.Columns.Contains("加价幅度"))
            {
                strErrors.Append("【加价幅度】");
            }
            if (!dt.Columns.Contains("原土地使用人"))
            {
                strErrors.Append("【原土地使用人】");
            }
            if (!dt.Columns.Contains("土地性质"))
            {
                strErrors.Append("【土地性质】");
            }
            if (!dt.Columns.Contains("是否配建保障房"))
            {
                strErrors.Append("【是否配建保障房】");
            }
            if (!dt.Columns.Contains("配建保障房情况"))
            {
                strErrors.Append("【配建保障房情况】");
            }
            if (!dt.Columns.Contains("保障房面积"))
            {
                strErrors.Append("【保障房面积】");
            }
            if (!dt.Columns.Contains("保障房类型（直接填写对应类型名称，经济适用房、廉租房、公共租赁房、定向安置房、限价房）"))
            {
                strErrors.Append("【保障房类型（直接填写对应类型名称，经济适用房、廉租房、公共租赁房、定向安置房、限价房）】");
            }
            if (!dt.Columns.Contains("起始时间"))
            {
                strErrors.Append("【起始时间】");
            }
            if (!dt.Columns.Contains("截止时间"))
            {
                strErrors.Append("【截止时间】");
            }
            if (!dt.Columns.Contains("公告编号"))
            {
                strErrors.Append("【公告编号】");
            }
            if (!dt.Columns.Contains("出让文件"))
            {
                strErrors.Append("【出让文件】");
            }
            if (!dt.Columns.Contains("供地附图"))
            {
                strErrors.Append("【供地附图】");
            }
            if (!dt.Columns.Contains("供地方式"))
            {
                strErrors.Append("【供地方式】");
            }
            if (!dt.Columns.Contains("状态"))
            {
                strErrors.Append("【状态】");
            }
            if (!dt.Columns.Contains("四至范围"))
            {
                strErrors.Append("【四至范围】");
            } 
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel表数据不正常,缺少以下数据列:\r\n" +
                                    strErrors);
                return false;
            }
            return true;
        }
        #endregion
    }
}